from util_date import SIMPLE_DATE_TIME_FMT
from utils_db import get_conn, close_conn


def get_boss_records():
    conn = get_conn()
    cph_info = {'A751MT': 'AUDI', 'AF98730': 'BYD', 'A81031': 'TOYOTA'}
    cursor = conn.cursor()
    for k, v in cph_info.items():
        SQL_QUERY = f"""
        SELECT * FROM [dbo].[MYCARCOMERECORD] WHERE CPH LIKE '%{k}%'
        """
        cursor.execute(SQL_QUERY)
        records = cursor.fetchone()
        if records is None:
            goout_query = f"""
            SELECT TOP 1 * FROM [dbo].[MYCARGOOUTRECORD] WHERE CPH LIKE '%{k}%' ORDER BY InTime DESC
            """
            cursor.execute(goout_query)
            out_records = cursor.fetchone()
            cph_info[k] = f'[{v}][🔴 Down]\n'
            if out_records is not None:
                cph_info[k] += out_records.InTime.strftime(
                    (SIMPLE_DATE_TIME_FMT)) + '---' + out_records.OutTime.strftime(SIMPLE_DATE_TIME_FMT)
        else:
            cph_info[k] = f'[{v}][✅ Up]\n' + records.InTime.strftime(SIMPLE_DATE_TIME_FMT)
    close_conn(conn)
    return cph_info


def check_in_and_out_time(cph):
    time_dict = {'InTime': '', 'OutTime': ''}
    conn = get_conn()
    cursor = conn.cursor()
    QUERY_IN = f"""
    SELECT * FROM [dbo].[MYCARCOMERECORD] WHERE CPH LIKE '%{cph}%'
    """
    goout_query = f"""
    SELECT TOP 1 * FROM [dbo].[MYCARGOOUTRECORD] WHERE CPH LIKE '%{cph}%' ORDER BY InTime DESC
    """
    cursor.execute(QUERY_IN)
    records = cursor.fetchone()
    if records is not None:
        time_dict['InTime'] = records.InTime.strftime(SIMPLE_DATE_TIME_FMT)
    cursor.execute(goout_query)
    records = cursor.fetchone()
    if records is not None:
        time_dict['OutTime'] = records.OutTime.strftime(SIMPLE_DATE_TIME_FMT)
    close_conn(conn)
    return time_dict

# if __name__ == '__main__':
#     dict = check_in_and_out_time('粤DA939D')
#     print( dict['InTime'] =='')